﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Web.Script.Services;
using System.Web.Services;
using System.IO;
using System.Data.OleDb;

namespace Ebiz
{
    public partial class BulkUpload : System.Web.UI.Page
    {
        public string AppList = "";
        enum BulkMode { 
            BulkMaster, BulkDetail
        }
        public string strWarning = "";
        protected void Page_Load(object sender, EventArgs e){
            string ExcelConnection = "";
            if (!IsPostBack) {
                if (isBulkUploading()){
                    string showErr = "<script>alert('Bulk Upload is in Progress!.Please try again');";
                    showErr += "window.location='Home.aspx';</script>";
                    Response.Write(showErr);
                    SetBulkStatus(0);
                }else{
                    MaxDistributor();
                    txtCLNumber.Text = SetCLPrefix();
                }
            }else if (IsPostBack) {
                //Show Warning Message
                try
                {
                    ShowWarningMsg();

                    if (fileCL.HasFile)
                    {
                        string TPath = Server.MapPath("~/images/upload/");
                        if (!Directory.Exists(TPath))
                        {
                            Directory.CreateDirectory(TPath);
                        }
                        int tmpFSize = fileCL.PostedFile.ContentLength;
                        double fSize = Math.Round((double)(tmpFSize / 1024), 2, MidpointRounding.AwayFromZero);
                        double RealFSize = (fSize > 1024) ? Math.Round((fSize / 1024), 2, MidpointRounding.AwayFromZero) : fSize;
                        string fUnit = (fSize > 1024) ? " MB" : " KB";

                        if (fSize > 5120)
                        {
                            string showErr = "<script>alert('Error: File Size Exeeds Limit of 5MB!');return false;</script>";
                            Response.Write(showErr);
                        }
                        else
                        {
                            string DistributorCode = cbDistributor.SelectedValue;
                            string CLNum = txtCLNumber.Text + cbDistributor.SelectedItem.ToString();
                            string ApprovalList = txtAppList.Value;

                            if (isCLExist(CLNum))
                            {
                                string showErr = "<script>alert('Error: Bulk Upload Already Exist!');window.location='BulkUpload.aspx';</script>";
                                Response.Write(showErr);
                            }
                            else
                            {
                                string tmpFileName = fileCL.PostedFile.FileName;
                                string fixFileName = RenameIfExist(tmpFileName);
                                string[] nFile = fixFileName.Split(new string[] { "\\" }, StringSplitOptions.None);
                                string fileName = TPath + nFile[nFile.Count() - 1];
                                string fileSize = fSize.ToString();
                                string fType = fileCL.PostedFile.ContentType.ToString();

                                ExcelConnection = setExcelConn(fileName, fType);
                                if (ExcelConnection == string.Empty || ExcelConnection == "INVALID")
                                {
                                    string showErr = "<script>alert('Error: Invalid File Type!');window.location='BulkUpload.aspx';</script>";
                                    Response.Write(showErr);
                                }
                                else
                                {
                                    SetBulkStatus(1);
                                    DeleteBulk(BulkMode.BulkMaster);
                                    DeleteBulk(BulkMode.BulkDetail);

                                    fileCL.SaveAs(fileName);
                                    Dictionary<string, object> dctResult = UploadData(ExcelConnection, fileName, CLNum);
                                    int bulkStatus = Convert.ToInt32(dctResult["Status"].ToString());
                                    if (bulkStatus == 1)
                                    {
                                        Dictionary<string, string> dctFileInfo = new Dictionary<string, string>();
                                        dctFileInfo.Add("CLNum", CLNum);
                                        dctFileInfo.Add("FileLocation", "images/upload/");
                                        dctFileInfo.Add("FileName", fixFileName);
                                        dctFileInfo.Add("FileType", GetFileExtension(fType));
                                        dctFileInfo.Add("FileSize", fileSize);

                                        SaveBulkMaster(CLNum, DistributorCode, ApprovalList, dctFileInfo);
                                    }
                                    else
                                    {
                                        //TO DO: Rollback here
                                        SetBulkStatus(0);
                                        string showErr = "<script>alert('Error: Bulk Upload Failed!');window.location='BulkUpload.aspx';</script>";
                                        Response.Write(showErr);
                                    }
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }
        }

        private void ShowWarningMsg(){
            strWarning = "<p>Please wait, processing bulk upload...</p>" +
                "<div style='color:Red;font-weight:bold;'>WARNING:</div>" +
                "<div style='color:Red;'>" +
                "    While Uploading, <strong>DO NOT</strong>:<br />" +
                "    <ul>" +
                "        <li>Click any buttons</li>" +
                "        <li>Close or Leave the page</li>" +
                "        <li>Restart or Turn of your computer</li>" +
                "    </ul>" +
                "</div>";
        }

        private void SaveBulkMaster(string CLNumber, string DistributorCode, 
            string ApprovalList, Dictionary<string,string> dctFile) {

            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
            string[] appList = ApprovalList.Split(',');
            int appListLen = appList.Length;
            string ApprovalCurrent = appList[0];
            string ApprovalNext = appList[1];
            int ApprovalMax = appList.Length;
            string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);

            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_BulkMaster (CLNum, DistributorCode, " +
                "Channel, IDControl, Value1, ApprovalCurrent, ApprovalNext, ApprovalStep, ApprovalMax, " +
                "CreatedBy, CreatedDate, StatusCode) VALUES " +
                "('" + CLNumber + "', '" + DistributorCode + "', 'HOS', 'CHAN', 'HOS', '" + ApprovalCurrent + 
                "', '" + ApprovalNext + "', 1, " + ApprovalMax + ", '" + CreatedBy + "', getdate(), '210')";
            
            if (ExecQuery(sql) > 0) { 
                //Copy view mapBulkCopy to: tbl_Request, tbl_RequestProduct, tbl_RequestCustomer
                //tbl_RequestApproval, tbl_RequestFiles
                List<string> appPersonList = appList.ToList<string>();
                SaveRequestCL(CLNumber, appPersonList, dctFile);

            }//Else TO DO: Rollback here
        }

        private void SaveRequestCL(string CLNumber, List<string> ApprovalList, 
            Dictionary<string, string> dctFile){

            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
            string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);

            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_Request (CLNum, DistributorCode, " +
                "CLCustName, CustomerGroup, Channel, DateRequest, DateStart, DateEnd, " +
                "DiscReason, Token, IDControl, Value1, ApprovalCurrent, ApprovalNext, " +
                "ApprovalStep, ApprovalMax, CreatedBy, CreatedDate, StatusCode) " +
                "SELECT TableSource.CLNum, TableSource.DistributorCode, " +
                "'As Attached', 'As Attached', 'HOS', getdate(), " +
                "(SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), " +
                "(SELECT DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)))), " +
                "'As Attached', '" + Token + "', 'CHAN', 'HOS', " +
                "TableSource.ApprovalCurrent, TableSource.ApprovalNext, " +
                "TableSource.ApprovalStep, TableSource.ApprovalMax, '" +
                CreatedBy + "', getdate(), '210' " +
                "FROM " + DBName + ".dbo.tbl_BulkMaster As TableSource";

            if (ExecQuery(sql) > 0){
                //Copy view mapBulkCopy to: tbl_Request, tbl_RequestProduct, tbl_RequestCustomer
                //tbl_RequestApproval, tbl_RequestFiles

                SaveRequestFile(CLNumber, ApprovalList, dctFile);

            }else{
                //TODO else Rollback here
                string showErr = "<script>alert('" + sql + "');window.location='BulkUpload.aspx';</script>";
                Response.Write(showErr);
            }
        }

        private void SaveRequestFile(string CLNumber, List<string> ApprovalList, 
            Dictionary<string, string> dctFile){

            string DBName = DBUtilities.GetDBName("devCon");
            
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestFiles " + 
                "(CLNum, FileLocation, FileName, FileType, FileSize, StatusCode) VALUES " + 
                "('" + dctFile["CLNum"] + "', @FileLocation, @FileName, '" + 
                dctFile["FileType"] + "', '" + dctFile["FileSize"] + "', '101')";
            /*
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestFiles " +
                "(CLNum, FileLocation, FileName, FileType, FileSize, StatusCode) VALUES " +
                "('" + dctFile["CLNum"] + "', '" + dctFile["FileLocation"] + "', '" + dctFile["FileName"] + "', '" +
                dctFile["FileType"] + "', '" + dctFile["FileSize"] + "', '101')";
            
            Response.Write(sql);
            Response.End();
            */
            Dictionary<string, object> dctParams = new Dictionary<string, object>();
            dctParams.Add("FileLocation", dctFile["FileLocation"]);
            dctParams.Add("FileName", dctFile["FileName"]);

            if (ExecQuery(sql, dctParams) > 0){
                SaveApproval(CLNumber, ApprovalList);
            }else{
                //TODO else Rollback here
                string showErr = "<script>alert('" + sql + "');window.location='BulkUpload.aspx';</script>";
                Response.Write(showErr);
            }
        }

        private void SaveApproval(string CLNumber, List<string> ApprovalList){

            bool isError = false;

            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
            string DBName = DBUtilities.GetDBName("devCon");
            int MaxStep = ApprovalList.Count;
            for (int idx = 0; idx < MaxStep; idx++) {
                int StepSeq = (idx + 1);
                string CurrentStep = ApprovalList[idx];
                string EmailAddress = GetEmailAddress(CurrentStep);

                string NextStep = "";
                if(idx < (MaxStep - 1)){
                    NextStep = ApprovalList[(idx + 1)];
                }else{
                    NextStep = "0";
                }

                string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestApproval (CLNum, StepSeq, " +
                    "CurrentStep, NextStep, EmailAddress, Token, StatusCode) " + 
                    "VALUES (@CLNum, " + StepSeq + ", @CurrentStep, @NextStep, @EmailAddress, " + 
                    "'" + Token + "', '210')";
                Dictionary<string, object> dctParams = new Dictionary<string, object>();
                dctParams.Add("CLNum", CLNumber);
                dctParams.Add("CurrentStep", CurrentStep);
                dctParams.Add("NextStep", NextStep);
                dctParams.Add("EmailAddress", EmailAddress);

                int ExecResult = ExecQuery(sql, dctParams);
                if (ExecResult < 1) {
                    isError = true;
                    break;
                }
            }

            if (!isError) { 
                //Redirect Page for Advanced Processing
                string PostTarget = "PostBulk.aspx?postbulk=" + CLNumber;
                Response.Redirect(PostTarget, true);
            }else{
                //TO DO: Rollback here
                string showErr = "<script>alert('Error: Bulk Upload Failed!');window.location='BulkUpload.aspx';</script>";
                Response.Write(showErr);
            }
        }

        private string GetEmailAddress(string UserID) {
            string result = "";
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    string sql = "SELECT Email FROM " + DBName + ".dbo.UserAccounts " + 
                        "WHERE (UserName = @UserID)";

                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue("UserID", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch(Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }
        
        private static bool isCLExist(string CLNumber) {
            bool result = true;

            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "SELECT COUNT(CLNum) FROM " +
                DBName + ".dbo.tbl_Request WHERE " +
                "(CLNum = @CLNum)";
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            string countRow = rdr.GetValue(0).ToString();
                            int rowCount = Convert.ToInt32(countRow);
                            if (rowCount > 0){
                                result = true;
                            }else{ 
                                result = false;
                            }
                        }
                    }
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private void DeleteBulk(BulkMode bulkMode) {
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "";
            switch(bulkMode){
                case BulkMode.BulkMaster:
                    sql = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkMaster";
                    break;
                case BulkMode.BulkDetail:
                    sql = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkDetail";
                    break;
            }
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private static Dictionary<string, object> UploadData(string ExcelCNString, string ExcelPath, 
            string CLNumber){

            Dictionary<string, object> result = new Dictionary<string, object>();

            int responseStatus = 0;
            string responseMsg = "";

            OleDbConnection ExcelCN = new OleDbConnection(ExcelCNString);
            using (ExcelCN){
                ExcelCN.Open();

                string sheetName = ExcelCN.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                string ExcelSelect = "SELECT '" + CLNumber + "' AS CLNum, * ";
                ExcelSelect += "FROM [" + sheetName + "]";
                OleDbCommand ExcelCmd = new OleDbCommand(ExcelSelect, ExcelCN);
                OleDbDataReader ExcelReader = ExcelCmd.ExecuteReader();

                string DBName = DBUtilities.GetDBName("devCon");
                string strCon = DBUtilities.strConnection("DevCon");
                SqlConnection cn = new SqlConnection(strCon);
                using (cn){
                    try{
                        cn.Open();

                        SqlTransaction sqlTransaction = cn.BeginTransaction();
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, sqlTransaction);
                        using (bulkCopy){
                            bulkCopy.DestinationTableName = DBName + ".dbo.tbl_BulkDetail";

                            bulkCopy.ColumnMappings.Add(0, "CLNum");
                            bulkCopy.ColumnMappings.Add(1, "ProductCode");
                            bulkCopy.ColumnMappings.Add(2, "ProdDivision");
                            //bulkCopy.ColumnMappings.Add(3, "ProductName");
                            bulkCopy.ColumnMappings.Add(4, "Discount");
                            bulkCopy.ColumnMappings.Add(5, "Quantity");
                            bulkCopy.ColumnMappings.Add(6, "CustGroup");
                            //bulkCopy.ColumnMappings.Add(7, "CustCode");
                            bulkCopy.ColumnMappings.Add(8, "CSCodeAZI");
                            bulkCopy.ColumnMappings.Add(9, "CustName");
                            bulkCopy.ColumnMappings.Add(10, "CustAddress");

                            bulkCopy.BatchSize = 100;
                            try{
                                bulkCopy.WriteToServer(ExcelReader);
                                sqlTransaction.Commit();

                                responseStatus = 1;
                                responseMsg = "Success";

                            }catch (Exception ex){
                                sqlTransaction.Rollback();
                                responseMsg = ex.Message;
                            }finally{
                                bulkCopy.Close();
                            }
                        }
                        ExcelReader.Close();
                    }catch (Exception ex){
                        responseMsg = ex.Message;
                    }finally{
                        if (cn.State == ConnectionState.Open){
                            cn.Close();
                        }
                    }
                }
            }
            result.Add("Status", responseStatus);
            result.Add("Message", responseMsg);

            return result;
        }

        private static string GetFileExtension(string fileType) {
            string result = "";
            switch (fileType) {
                case "application/vnd.ms-excel":
                    result = "xls";
                    break;
                case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                    result = "xlsx";
                    break;
                default:
                    result = "NULL";
                    break;
            }
            return result;
        }

        private static string setExcelConn(string fileName, string fileType){
            return setExcelConn(fileName, fileType, true);
        }

        private static string setExcelConn(string fileName, string fileType, bool HDR){

            string result = "INVALID";
            if ((fileName != null || fileName != string.Empty) &&
                (fileType != null || fileType != string.Empty)){

                string incHDR = (HDR == true) ? ";HDR=YES" : "";
                try { 
                    switch (fileType){
                        case "application/vnd.ms-excel":
                            result = "Provider=Microsoft.Jet.OLEDB.4.0;";
                            result += "Data Source=" + fileName + ";";
                            result += "Extended Properties='Excel 8.0" + incHDR + "'";
                            break;
                        case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                            result = "Provider=Microsoft.ACE.OLEDB.12.0;";
                            result += "Data Source=" + fileName + ";";
                            result += "Extended Properties='Excel 8.0" + incHDR + "'";
                            break;
                        default:
                            result = "INVALID";
                            break;
                    }
                }
                catch (Exception ex) {
                    result = ex.Message;
                }
            }else{
                result = string.Empty;
            }
            return result;
        }

        private static string RenameIfExist(string fileName){
            string result = string.Empty;
            string[] nmFile = fileName.Split(new string[] { "\\" }, StringSplitOptions.None);
            string[] tmpFile = fileName.Split(new string[] { "." }, StringSplitOptions.None);
            if (File.Exists(HttpContext.Current.Server.MapPath("~/images/upload/" + nmFile[nmFile.Count()-1]))){
                Random rnd = new Random();
                int numExt = rnd.Next(1, 9999);
                string sExt = numExt.ToString().PadLeft(4, '0');

                var tmpResult = "";
                int arrLen = tmpFile.Length;
                for (var idx = 0; idx < arrLen; idx++){
                    if (idx < (arrLen - 1)){
                        tmpResult += tmpFile[idx].ToString();
                    }else if (idx == (arrLen - 1)){
                        tmpResult += sExt + "." + tmpFile[idx].ToString();
                    }
                }
                result = tmpResult;
            }else{
                result = fileName;
            }
            return result;
        }

        protected void MaxDistributor() {
            string dbName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                try {
                    sqlCon.Open();

                    string strQuery = "SELECT DistributorCode FROM " +
                    dbName + ".dbo.mstDistributor";
                    SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                    SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                    if (sqlReader.HasRows){
                        int idx = 0;
                        while (sqlReader.Read()) {
                            idx++;

                            string tmpValue = idx.ToString();
                            string cbValue = tmpValue.PadLeft(3, '0');
                            string strCode = sqlReader.GetValue(0).ToString();
                            if (idx == 1) {
                                SetInitDistributor(strCode);
                            }
                            
                            cbDistributor.Items.Add(new ListItem(cbValue, strCode));
                        }
                    }
                }catch (Exception ex) {
                    string errMsg = ex.Message;
                }finally {
                    if (sqlCon.State == ConnectionState.Open){
                        sqlCon.Close();
                    }
                }
            }
        }

        protected void SetInitDistributor(string DistributorCode) {
            Dictionary<string, string> dctTemp = GetDistributorName(DistributorCode);
            hidDistributor.Value = dctTemp["Code"];
            txtDistributor.Text = dctTemp["Name"];
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string SetCLPrefix(){
            string CLPrefix = "AZI/HOS/";
            string CLYear = DateTime.Now.AddYears(0).ToString("yy");
            string CLCode = CLPrefix + CLYear + "/";

            return CLCode;
        }

        protected void SetBulkStatus(int BulkStatus) {
            string DBName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(strCon);
            string sql = "UPDATE " + DBName + ".dbo.tbl_BulkProcess " + 
                "SET BulkProgress = " + BulkStatus + " WHERE " + 
                "IDSeq = 1";
            using (cn){
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    string errMsg = ex.Message;    
                }
                finally {
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        protected static bool isBulkUploading() {
            bool result = true;

            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);

            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT BulkProgress FROM dbo.tbl_BulkProcess WHERE IDSeq = 1";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    if (sqlReader.Read()){
                        int tmp = Convert.ToInt32(sqlReader.GetValue(0).ToString());
                        if (tmp == 0){
                            result = false;
                        }
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, string> GetDistributorName(string DistributorCode){
            Dictionary<string, string> result = new Dictionary<string, string>();

            string dbName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                try {
                    sqlCon.Open();

                    string strQuery = "SELECT DistributorName FROM " +
                        dbName + ".dbo.mstDistributor WHERE (DistributorCode = '" + DistributorCode + "')";
                    SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                    SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                    if (sqlReader.HasRows){
                        if (sqlReader.Read()){
                            string strName = sqlReader.GetValue(0).ToString();

                            result.Add("Code", DistributorCode);
                            result.Add("Name", strName);
                        }
                    }
                }
                catch (Exception ex) {
                    string errMsg = ex.Message;
                }
                finally {
                    if (sqlCon.State == ConnectionState.Open){
                        sqlCon.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> GetRuleList(){
            Dictionary<string, object> result = new Dictionary<string, object>();

            int _ruleCount = 0;
            string _MaxSteps = "0";

            string dbName = DBUtilities.GetDBName("devCon");
            string RuleQuery = "SELECT COUNT(IDControl) FROM " + dbName + ".dbo.tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = 'CH') AND " +
                "(Value1 = @Value1)";

            string[] appValue = SetCLPrefix().Split('/');
            Dictionary<string, object> lstParamsCH = new Dictionary<string, object>();
            lstParamsCH.Add("@Value1", (appValue.Count()==1)?"HOS":appValue[1]);
            _ruleCount = RuleCount(RuleQuery, lstParamsCH);

            int _tmpMaxStepsCH = MaxSteps();
            if (_tmpMaxStepsCH > 0){
                result.Add("RuleExist", "1");

                _MaxSteps = _tmpMaxStepsCH.ToString();
                result.Add("MaxSteps", _MaxSteps);
                result.Add("AppList", GetApprovalList(1, string.Empty));
            }else{
                result.Add("RuleExist", "0");
            }

            return result;
        }

        private static int RuleCount(string QueryString){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(QueryString, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string countRow = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(countRow);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static int RuleCount(string QueryString, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(QueryString, cn);
                foreach (KeyValuePair<string, object> dict in Params){
                    cmd.Parameters.AddWithValue(dict.Key, dict.Value);
                }
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string countRow = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(countRow);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static int MaxSteps(){

            int result = 0;
            string[] appValue = SetCLPrefix().Split('/');
            string app = (appValue.Count() == 1) ? "HOS" : appValue[1];
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT ISNULL(MAX(StepSeq), 0) AS MaxSteps FROM " +
                dbName + ".dbo.tbl_ApprovalStep WHERE " +
                "(IDControl = 'CHAN') AND (Value1 = '"+app+"') AND " +
                "(Value2 = '') AND (NextStep <> '0')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string rval = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(rval);
                    }
                }else{
                    result = 0;
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> GetApprovalList(int ApprovalLayer, string CurrentApproval){

            List<object> result = new List<Object>();
            string xs = "";
            Dictionary<string, string> dctAppStep = new Dictionary<string, string>();
            if (string.IsNullOrEmpty(CurrentApproval)){
                dctAppStep = AppStepList(ApprovalLayer);
            }else{
                dctAppStep = AppStepList(ApprovalLayer, CurrentApproval);
            }

            string sRole = "";
            string sUname = "";
            foreach (KeyValuePair<string, string> oAppList in dctAppStep){
                if (oAppList.Key == "ROLE"){
                    sRole = oAppList.Value;
                }else if (oAppList.Key == "UNAME"){
                    sUname = oAppList.Value;
                }else{
                    xs = oAppList.Value;
                }
            }

            string xRole = (string.IsNullOrEmpty(sRole)) ? "'0'" : sRole;
            string xUname = (string.IsNullOrEmpty(sUname)) ? "''" : sUname;

            string qry = "SELECT UserName, FullName " +
                "FROM dbo.UserAccounts WHERE (AppActor = 'Y') AND (StatusCode = '101') AND " +
                "((IDRole IN (" + xRole + ")) OR " +
                "(UserName IN (" + xUname + "))) " +
                "ORDER BY FullName";

            //result.Add(qry);
            result = AppUserList(qry);
            return result;
        }

        private static Dictionary<string, string> AppStepList(int ApprovalLayer){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                string qry = "SELECT NextStep FROM dbo.tbl_ApprovalStep " +
                    "WHERE (IDControl = 'CHAN') AND (Value1 = 'NPP') " +
                    "AND (Value2 = '') " + "AND (StepSeq = " + ApprovalLayer + ") " +
                    "GROUP BY NextStep";
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }
                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static Dictionary<string, string> AppStepList(int ApprovalLayer, string CurrentApproval){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                string xtra = " AND ((CurrentStep = '" + CurrentApproval + "') OR ";
                xtra += "(CurrentStep = CONVERT(NVARCHAR,(SELECT IDRole FROM dbo.UserAccounts WHERE UserName = '" + CurrentApproval + "')))) ";
                string qry = "SELECT NextStep FROM dbo.tbl_ApprovalStep " +
                    "WHERE (IDControl = 'CHAN') AND (Value1 = 'NPP') " +
                    "AND (Value2 = '') " + "AND (StepSeq = " + ApprovalLayer + ") " + xtra +
                    "GROUP BY NextStep";

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }

                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static List<object> AppUserList(string Query){
            List<object> result = new List<object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(Query, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string dctKey = rdr.GetValue(0).ToString();
                        string dctVal = rdr.GetValue(1).ToString();

                        Dictionary<string, object> dctResult = new Dictionary<string, object>();
                        dctResult.Add("appKey", dctKey);
                        dctResult.Add("appVal", dctVal);

                        result.Add(dctResult);
                    }
                }
                cn.Close();
            }
            return result;
        }
        
    }
}
